<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>关于mysql一些优化心得 | 失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		<article>
	
		<h1>关于mysql一些优化心得</h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>先介绍下服务器架构及配置8核8G，10M带宽Centos6.5 64</strong></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>Nginx&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;1.8.1PHP   &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;5.3.29Mysql&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;5.5.42</strong></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;一电商网站后台查询订单时 经常php超时，导致php报错以下是排查过程</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;一、php执行超时，首先我们想到的就是php.ini文件中max_execution_time =  #把默认的值调整了下</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;二、然后在后台执行订单查询php不报错了，但是查询耗时较长，用时65s.  而且一些表成锁死状态碎片比较多，本人对mysql数据库优化不是很了解，于是百度了一下：一般mysql调优主要是根据慢查询日志去优化sql语句，my.cnf里面没啥可调的。 下面就是分析mysql慢日志,调整参数3、mysql参数优化，主要调整的参数如下。根据机器性能来调整，如果你对参数不是很了解，建议不要盲目的调</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;把一些配置文件修改好后重启相关服务，由原来的65s变成了十几秒。效果还是不是很理想，查看了下mysql默认引擎为MyISAM，决定把引擎改为Innodb</p>
<ol>
<li>导出shop数据库的表结构</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump -d -uxxx -p shop &gt; shop_table.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;其中-d参数表示不导出数据，只导出表结构</p>
<ol>
<li>替换shop_table.sql里的MyISAM为INNODB</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">sed -i <span class="string">'s/MyISAM/INNODB/g'</span> shop_table.sql</div></pre></td></tr></table></figure>
<ol>
<li>新建数据库shop_new,并导入表结构</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql &gt; create database shop_new;mysql -uroot -p shop_new &lt; shop_table.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以通过show table status来检查表引擎是否为INNODB。</p>
<ol>
<li>导出shop的数据</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump -t -uroot -p shop &gt; shop_data.sql</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;其中-t参数表示只导数据，不导表结构</p>
<ol>
<li>导入数据到</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">shop_newmysql -uroot -p shop_new &lt; shop_data.sql</div></pre></td></tr></table></figure>
<ol>
<li>首先开启慢日志，修改/etc/my.cnf  增加以下两段配置，保存重启mysql</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">vim /etc/my.cnf</div><div class="line"></div><div class="line">long_query_time = 2</div><div class="line">log_slow_queries = /data/mysql/slow.log</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;重启mysql服务</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">service mysqld restart</div></pre></td></tr></table></figure>
<ol>
<li>查看慢日志来定位mysql哪条语句执行慢，然后建立索引，优化sql执行语句。</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div></pre></td><td class="code"><pre><div class="line">&lt;font color=<span class="string">"Red"</span>&gt;tail -n20 /data/mysql/slow.log <span class="comment">#查看20行&lt;/font&gt;</span></div><div class="line"> </div><div class="line"> </div><div class="line"> <span class="comment"># Time: 160303 12:12:38</span></div><div class="line"> <span class="comment"># User@Host: root[root] @ [10.165.34.182]</span></div><div class="line"> <span class="comment"># Query_time: 10.145685 Lock_time: 0.000395 Rows_sent: 1 Rows_examined: 24306970</span></div><div class="line"> use shop;</div><div class="line">SET timestamp=1456978358;</div><div class="line"> SELECT COUNT(*) FROM `shop`.`ecs_order_info` o LEFT JOIN`shop`.`ecs_users` u ON o.user_id = u.user_id LEFT JOIN `shop`.`ecs_affiliate_log` a ON o.order_id = a.order_id WHERE o.user_id &gt; 0 AND (u.parent_id &gt; 0 AND o.is_separate = 0 OR o.is_separate &gt; 0);</div><div class="line"> <span class="comment"># Time: 160303 12:12:44</span></div><div class="line"> <span class="comment"># User@Host: root[root] @ [10.165.34.182]</span></div><div class="line"> <span class="comment"># Query_time: 6.073441 Lock_time: 0.000152 Rows_sent: 15 Rows_examined: 24314767</span></div><div class="line">SET timestamp=1456978364;</div><div class="line">SELECT o.*, a.log_id, a.user_id as suid, a.user_name as auser, a.money, a.point, a.separate_type,u.parent_id as up FROM `shop`.`ecs_order_info` o LEFT JOIN`shop`.`ecs_users` u ON o.user_id = u.user_id LEFT JOIN `shop`.`ecs_affiliate_log` a ON o.order_id = a.order_id WHERE o.user_id &gt; 0 AND (u.parent_id &gt; 0 AND o.is_separate = 0 OR o.is_separate &gt; 0) ORDER BY order_id DESC LIMIT 0,15;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;通过慢日志发现其中有几个表查询耗时较长，下面就是把这个查询慢的表建立索引</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;用到的软件 NAvicat，对查询慢的表进行设计，增加索引</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/%E5%85%B3%E4%BA%8Emysql%E4%B8%80%E4%BA%9B%E4%BC%98%E5%8C%96%E5%BF%83%E5%BE%97/01.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;根据 explain 的解释，查看下索引是否建立，一般都是这样调整就行。</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/%E5%85%B3%E4%BA%8Emysql%E4%B8%80%E4%BA%9B%E4%BC%98%E5%8C%96%E5%BF%83%E5%BE%97/02.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;修改完后重启 mysql 服务，查询时间从65s,缩短到 0.017407 秒</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/%E5%85%B3%E4%BA%8Emysql%E4%B8%80%E4%BA%9B%E4%BC%98%E5%8C%96%E5%BF%83%E5%BE%97/03.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;参考了大量的网络资料，头一次搞优化。优化完后很有成就感，算是一次新的挑战</p>

	

	
		<span class="different-posts"><a href="/2017/10/12/MySQL/8. 关于mysql一些优化心得/" onclick="window.history.go(-1); return false;">⬅️ Go back </a></span>

	

</article>

	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
